In [1]:

    

import pandas as pd
import arcpy
import os
import sys
import numpy as np
arcpy.env.overwriteOutput = True


    

In [2]:

    

xlfile = "H:/GWP/Wetland/WaterMonitoring/PiezometerData/PiezometersCorrected_2015_1.xlsx"


    

In [3]:

    

xl = pd.ExcelFile(xlfile)


    

In [4]:

    

sde_conn = "C:/Users/{:}/AppData/Roaming/ESRI/Desktop10.5/ArcCatalog/UGS_SDE.sde".format(os.environ.get('USERNAME'))
arcpy.env.workspace = sde_conn


    

In [5]:

    

def add_data(df):
    gw_reading_table="UGGP.UGGPADMIN.UGS_GW_reading"
    
    read_descr = arcpy.Describe(gw_reading_table)
    fieldnames = []
    for field in read_descr.fields:
        fieldnames.append(field.name)
    fieldnames.remove('OBJECTID')
    fieldnames.remove('DELTALEVEL')
    table_names = df.columns
    
    for name in fieldnames:
        if name not in table_names:
            fieldnames.remove(name)

            
    if len(fieldnames) > 0:
        subset = df[fieldnames]
        rowlist = subset.values.tolist()

        arcpy.env.overwriteOutput = True
        edit = arcpy.da.Editor(arcpy.env.workspace)
        edit.startEditing(False, False)
        edit.startOperation()

        cursor = arcpy.da.InsertCursor(gw_reading_table, fieldnames)
        for j in range(len(rowlist)):
            cursor.insertRow(rowlist[j])

        del cursor
        edit.stopOperation()
        edit.stopEditing(True)
        print('Well {:} imported!'.format(df['LOCATIONID'].values[-1]))
    else:
        print('No data imported!')


    

In [6]:

    

xl.sheet_names


    

    
Out[6]:





['Combined',
 'LastEntryCompile',
 'Metadata',
 'FieldData',
 'PreviousDownloadLastLine',
 'BARO1',
 'BARO2',
 'BARO3',
 'BARO14',
 '1001',
 '1002',
 '1003',
 '1004',
 '1005',
 '1006',
 '1007',
 '1008',
 '1009',
 '1010',
 '1011',
 '1012',
 '1013',
 '1014',
 '1015',
 '1016',
 '1018',
 '1019',
 '1020',
 '1021',
 '1022',
 '1023',
 '1024',
 '1025',
 '1026',
 '1028',
 '1029',
 '1030',
 '1031',
 '1033',
 '1035',
 '1036',
 '1037',
 '1038',
 '1039',
 '1040',
 '1041',
 '1042',
 '1043',
 '1044',
 '1045',
 '1046',
 '1047',
 '1049',
 '1051',
 '1052',
 '1053',
 '1054',
 '1055',
 '1056',
 '1057',
 '1060',
 '1063',
 '1065',
 '1067',
 '1068',
 '1069',
 '1070',
 '1072',
 '1073',
 '1075',
 '1076',
 '1077',
 '1078',
 '1079',
 '1080',
 '1081',
 '1090',
 '1091',
 '1092',
 '1093',
 '1094',
 '1095',
 '1096',
 '1097',
 '1098',
 '2001',
 '2002',
 '2003',
 '3001',
 '3002',
 '3003']

In [7]:

    

xldict = {}
cols = ['READINGDATE','MEASUREDLEVEL','TEMP','BP','MEASUREDDTW','DRIFTCORRECTION','WATERELEVATION','DTWBELOWGROUNDSURFACE']

for sheet in xl.sheet_names:
    #print(sheet)
    if str(sheet)[:2] == '10':
        if int(str(sheet)[:4]) < 1081:
            try:
                xldict[sheet] = xl.parse(sheet, parse_cols=("A,B,C,D,E,L,M,N"), skiprows=1,header=None)
                xldict[sheet].columns = cols
                xldict[sheet]['TAPE'] = 0
                xldict[sheet]['LOCATIONID'] = int(sheet)
                if xldict[sheet].loc[xldict[sheet].last_valid_index(),'DRIFTCORRECTION'] < 0.3:
                    add_data(xldict[sheet])
                else:
                    print('Well {:} drift too high'.format(xldict[sheet]['LOCATIONID'].values[-1]))
            except (RuntimeError,TypeError):
                print('format exception for well {:}'.format(sheet))
                pass


    

    




Well 1001 imported!
Well 1002 imported!
Well 1003 imported!
Well 1004 imported!
Well 1005 drift too high
Well 1006 imported!
Well 1007 imported!
Well 1008 imported!
Well 1009 imported!
Well 1010 imported!
Well 1011 imported!
Well 1012 imported!
Well 1013 imported!
Well 1014 imported!
Well 1015 imported!
Well 1016 imported!
Well 1018 imported!
Well 1019 imported!
Well 1020 imported!
Well 1021 imported!
Well 1022 imported!
Well 1023 imported!
Well 1024 imported!
Well 1025 imported!
Well 1026 imported!
Well 1028 imported!
Well 1029 imported!
Well 1030 imported!
Well 1031 imported!






    




---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-7-2aef4b45af1d> in <module>()
     11                 xldict[sheet]['TAPE'] = 0
     12                 xldict[sheet]['LOCATIONID'] = int(sheet)
---> 13                 if xldict[sheet].loc[xldict[sheet].last_valid_index(),'DRIFTCORRECTION'] < 0.3:
     14                     add_data(xldict[sheet])
     15                 else:

TypeError: unorderable types: str() < float()

In [ ]:

    

xl.parse('1003', parse_cols=("A,B,C,D,E,L,M,N"), skiprows=1,header=None)